package com.budwk.app.sqfw.services.datamange.impl;

import cn.hutool.core.bean.BeanUtil;
import cn.hutool.core.date.DatePattern;
import cn.hutool.core.date.LocalDateTimeUtil;
import cn.hutool.core.util.ObjectUtil;
import cn.hutool.json.JSONArray;
import cn.hutool.json.JSONUtil;
import com.alibaba.fastjson.JSONObject;
import com.budwk.app.common.config.AppException;
import com.budwk.app.sqfw.constants.CommonForAdminConstants;
import com.budwk.app.sqfw.constants.DataManageConstants;
import com.budwk.app.sqfw.dto.TreeDTO;
import com.budwk.app.sqfw.dto.onlineExcel.SqfwExcelOnlineAllDTO;
import com.budwk.app.sqfw.dto.onlineExcel.SqfwFormSummaryOnlineDTO;
import com.budwk.app.sqfw.enums.datamange.DataManageHiddenDangersEnum;
import com.budwk.app.sqfw.enums.datamange.TypeToPKeyEnum;
import com.budwk.app.sqfw.models.Sqfw_datamange_community;
import com.budwk.app.sqfw.models.Sqfw_datamange_config;
import com.budwk.app.sqfw.models.Sqfw_formSummary_new;
import com.budwk.app.sqfw.services.SqfwFormsummaryNewService;
import com.budwk.app.sqfw.services.datamange.DataMangeCommunityService;
import com.budwk.app.sqfw.services.datamange.DataMangeConfigService;
import com.budwk.app.sqfw.utils.CommunityDataCleanUtils;
import com.budwk.app.sqfw.vo.SqfwDataMangeVO;
import com.budwk.app.sys.models.Sys_area;
import com.budwk.app.sys.models.Sys_user;
import com.budwk.app.sys.services.SysAreaService;
import com.budwk.app.sys.services.SysUserService;
import com.budwk.app.util.ThreadPoolExecutorUtil;
import com.budwk.app.util.poiUtils.nutzBeanUtils.NutzIocUtils;
import com.budwk.starter.common.page.Pagination;
import com.budwk.starter.common.result.ResultCode;
import com.budwk.starter.database.service.BaseServiceImpl;
import com.budwk.starter.security.utils.SecurityUtil;
import lombok.extern.slf4j.Slf4j;
import org.apache.commons.lang3.ObjectUtils;
import org.apache.commons.lang3.StringUtils;
import org.nutz.aop.interceptor.ioc.TransAop;
import org.nutz.dao.Chain;
import org.nutz.dao.Cnd;
import org.nutz.dao.Dao;
import org.nutz.dao.Sqls;
import org.nutz.dao.sql.Sql;
import org.nutz.dao.sql.SqlCallback;
import org.nutz.dao.util.cri.Static;
import org.nutz.ioc.aop.Aop;
import org.nutz.ioc.loader.annotation.Inject;
import org.nutz.ioc.loader.annotation.IocBean;
import org.nutz.lang.Strings;
import org.nutz.lang.random.R;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.*;
import java.util.concurrent.CompletableFuture;
import java.util.concurrent.ConcurrentSkipListMap;
import java.util.concurrent.ExecutorService;
import java.util.concurrent.ThreadPoolExecutor;
import java.util.concurrent.atomic.AtomicInteger;

@Slf4j
@IocBean(args = {"refer:dao"})
//@CacheDefaults(cacheName = RedisConstant.WKCACHE + "shequ.sqfw_data_mange", isHash = true, cacheLiveTime = 120)
public class DataMangeCommunityServiceImpl extends BaseServiceImpl<Sqfw_datamange_community> implements DataMangeCommunityService {
    public DataMangeCommunityServiceImpl(Dao dao) {
        super(dao);
    }

    @Inject
    private SysUserService sysUserService;

    @Inject
    private SqfwFormsummaryNewService sqfwFormsummaryNewService;

    @Inject
    private DataMangeConfigService dataMangeConfigService;

    @Inject
    private SysAreaService sysAreaService;


    //@CacheResult
    public Pagination page(SqfwDataMangeVO bean) {
        if (null == bean) {
            bean = new SqfwDataMangeVO();
        }
        String type = bean.getType();
        long startTime = System.currentTimeMillis();
        // 构造查询条件
        List<Sqfw_datamange_community> sqfwFormSummaryOnlines = new ArrayList<Sqfw_datamange_community>();
        // 查询header头
        Cnd cnd = Cnd.NEW();
        cnd.and(Sqfw_datamange_community::getType, "=", type);
        cnd.and(Sqfw_datamange_community::getExcelSeg, "=", "header");
        List<Sqfw_datamange_community> sqfwFormSummaryOnlines1 = query(cnd);
        if (sqfwFormSummaryOnlines1.size() <= 0) {
            return new Pagination();
        }
        Sqfw_datamange_community sqfwFormSummaryOnline = sqfwFormSummaryOnlines1.get(0);
        sqfwFormSummaryOnlines.add(sqfwFormSummaryOnline);
        String sql = buildQueryCond(bean, sqfwFormSummaryOnline);
        Pagination pagination = this.listPage(bean.getPageNo(), bean.getPageSize(), Sqls.create(sql));
        List<Sqfw_datamange_community> sqfwOnlines = pagination.getList(Sqfw_datamange_community.class);
        sqfwOnlines.forEach(sqfwFormSummaryOnline1 -> {
            sqfwFormSummaryOnlines.add(sqfwFormSummaryOnline1);
        });
        pagination.setList(sqfwFormSummaryOnlines);
        log.info("DataMangeCommunityServiceImpl-list-耗时: {}ms", System.currentTimeMillis() - startTime);
        return pagination;
    }

    /**
     * 根据类型查询视图
     *
     * @param bean
     * @return
     */
    //@CacheResult
    public Pagination pageViewByType(SqfwDataMangeVO bean) {
        if (null == bean) {
            bean = new SqfwDataMangeVO();
        }
        if (StringUtils.isEmpty(bean.getSubType())) {
            bean.setSubType("all");
        }

        long startTime = System.currentTimeMillis();
        // 构造查询条件
        List<Sqfw_datamange_community> finalDatamanges = new ArrayList<Sqfw_datamange_community>();
        // 查询excel header头
        Sqfw_datamange_community sqfwDatamangeCommunity = queryHeaderInfo(bean);
        if (null == sqfwDatamangeCommunity) {
            return new Pagination();
        }
        finalDatamanges.add(sqfwDatamangeCommunity);

        // 查询excel body
        bean.setExcelSeg("body");
        String viewName = TypeToPKeyEnum.getViewTable(bean.getType(), bean.getSubType());
        int count = queryViewInfoCount(bean, viewName);
        finalDatamanges.addAll(queryViewInfo(bean, viewName, "false"));
        Pagination pagination = new Pagination();
        pagination.setList(finalDatamanges);
        pagination.setTotalCount(count);
        pagination.setPageNo(bean.getPageNo());
        pagination.setPageSize(bean.getPageSize());
        log.info("DataMangeCommunityServiceImpl-list-耗时: {}ms", System.currentTimeMillis() - startTime);
        return pagination;
    }

    /**
     * 新增数据
     *
     * @param bean
     * @return
     */
    @Override
    public void save(SqfwDataMangeVO bean) {
        Sqfw_datamange_community sqfwFormSummaryOnline = new Sqfw_datamange_community();
        BeanUtil.copyProperties(bean, sqfwFormSummaryOnline);
        sqfwFormSummaryOnline.setCreateDate(new Date());
        this.insert(sqfwFormSummaryOnline);
    }

    @Override
    @Aop(TransAop.READ_COMMITTED)
    public void deleteData(List<String> ids) {
        if (ids.size() > 0) {
            this.update(Chain.make("del_flag", true), Cnd.where("id", "IN", ids));
        }
        //this.clear();
    }

    @Override
    @Aop(TransAop.READ_COMMITTED)
    public void updateData(SqfwDataMangeVO bean) {
        Sqfw_datamange_community sqfwFormSummaryOnline = new Sqfw_datamange_community();
        sqfwFormSummaryOnline.setId(bean.getId());
        sqfwFormSummaryOnline.setExcelData(bean.getExcelData());
        updateIgnoreNull(sqfwFormSummaryOnline);
    }

    @Override
    //@CacheRemoveAll
    public void batchCleanData(SqfwDataMangeVO bean) {
        if (null == bean) {
            return;
        }
        StringBuffer sb = new StringBuffer(" 1 = 1 ");
        Cnd cnd = Cnd.NEW();
        if (StringUtils.isNotEmpty(bean.getProvince())) {
            sb.append(" AND province  = '").append(bean.getProvince()).append("'");
        }
        if (StringUtils.isNotEmpty(bean.getCity())) {
            sb.append(" AND city  = '").append(bean.getCity()).append("'");
        }
        if (StringUtils.isNotEmpty(bean.getDistrict())) {
            sb.append(" AND district  = '").append(bean.getDistrict()).append("'");
        }
        if (StringUtils.isNotEmpty(bean.getCounty())) {
            sb.append(" AND county  = '").append(bean.getCounty()).append("'");
        }
        if (StringUtils.isNotEmpty(bean.getCommunity())) {
            sb.append(" AND community  = '").append(bean.getCommunity()).append("'");
        }
        if (StringUtils.isNotEmpty(bean.getType())) {
            sb.append(" AND type  like '%").append(bean.getType()).append("%'");
        }
        sb.append(" LIMIT ").append(bean.getPageSize()).append(" OFFSET ").append(bean.getPageNo());

        List<Sqfw_formSummary_new> sqfwDatamangeCommunities = sqfwFormsummaryNewService.query(Cnd.where(new Static(sb.toString())));
        if (sqfwDatamangeCommunities.size() <= 0) {
            return;
        }
        cleanDataByType(bean.getIds(), bean.getType());
    }

    /**
     * 抽取数据3.0
     *
     * @param bean
     */
    @Override
    public void parseDataBig(SqfwDataMangeVO bean) {
/*        List<Sqfw_formSummary_new> sqfwFormSummaryNews = sqfwFormsummaryNewService.query(Cnd.where("type", "=", bean.getType()).limit(Integer.valueOf(bean.getColNum())));
        List<String> ids = sqfwFormSummaryNews.stream().map(Sqfw_formSummary_new::getId).collect(Collectors.toList());*/
        cleanDataBySummaryId(bean.getIds());
    }


    @Aop(TransAop.READ_COMMITTED)
    @Override
    public void cleanDataByType(List<String> ids, String type) {
        // 参数检查
        if (ObjectUtils.isEmpty(ids) || StringUtils.isEmpty(type)) {
            log.error("====ids或type参数空===={}，{}", ids.toString(), type);
            return;
        }
        // 配置开关检查
        Sqfw_datamange_config sqfwDatamangeConfig = dataMangeConfigService.fetch(
                Cnd.where("type", "LIKE", "%" + type + "%").and("sub_type", "=", "all").and(Sqfw_datamange_config::getDelFlag, "=", false));
        if (null == sqfwDatamangeConfig) {
            log.error("====data_config参数不存在或已失效===={}====", type);
            return;
        }
        List<Sqfw_formSummary_new> sqfwFormSummaryNewList = sqfwFormsummaryNewService.query(Cnd.where("id", "IN", ids));
        if (sqfwFormSummaryNewList.size() <= 0) {
            log.info("未查找到summary数据，{}", ids);
            return;
        }

        // 获取所有相关数据字段
        LinkedHashMap<String, String> dictFieldAll = CommunityDataCleanUtils.queryDictFieldInfoByAll();
        if (null == dictFieldAll) {
            log.info("===============未查询到对应的数据字典field all==============={}====", dictFieldAll.size());
            return;
        }

        // 如果是门前三包， 则需要去低代码config表中取 自定义字段 来和 总表字段汇总
        LinkedHashMap<String, String> fieldMap = new LinkedHashMap<>();
        String configJson = CommunityDataCleanUtils.queryDynamicFieldInfoByType(type);
        CommunityDataCleanUtils.parseDictFeildNew(configJson, fieldMap);
        log.info("==============动态字段config====={}={}={}====", type, configJson, JSONUtil.toJsonStr(fieldMap));

        // 检查数据管理配置表中是否有新字段变化
        CommunityDataCleanUtils.checkDataManageConfigField(type, "all", dictFieldAll, fieldMap);
        log.info("===============数据字段：{}===============", dictFieldAll.size());

        // 拼接全量字段数据
        CommunityDataCleanUtils.checkDataManageConfigPutFieldAll(dictFieldAll);

        List<SqfwFormSummaryOnlineDTO> onlineList = new ArrayList<SqfwFormSummaryOnlineDTO>();

        // 检查是 该类型是否有对应的excel头,没有才插入
        Sqfw_datamange_community sqfwDataMangeExcelHeader =
                this.fetch(Cnd.where(Sqfw_datamange_community::getExcelSeg, "=", "header"));
        // 如果没有头，则插入，如果有则更新
        if (ObjectUtils.isEmpty(sqfwDataMangeExcelHeader) || StringUtils.isEmpty(sqfwDataMangeExcelHeader.getExcelData())) {
            SqfwFormSummaryOnlineDTO sqfwFormSummaryOnlineDTO = new SqfwFormSummaryOnlineDTO();
            sqfwFormSummaryOnlineDTO.setExcelDataMap(dictFieldAll);
            onlineList.add(sqfwFormSummaryOnlineDTO);
        } else {
            LinkedList<SqfwFormSummaryOnlineDTO> onlineExcelHeaders = new LinkedList<SqfwFormSummaryOnlineDTO>();
            SqfwFormSummaryOnlineDTO sqfwFormSummaryOnlineDTO = new SqfwFormSummaryOnlineDTO();
            sqfwFormSummaryOnlineDTO.setExcelDataMap(dictFieldAll);
            sqfwFormSummaryOnlineDTO.setExcelSeg("header");
            onlineExcelHeaders.add(sqfwFormSummaryOnlineDTO);
            List<Sqfw_datamange_community> sqfwFormSummaryOnlines = Collections.synchronizedList(new ArrayList<Sqfw_datamange_community>());
            buildExcelData(onlineExcelHeaders, sqfwFormSummaryOnlines, type);
            sqfwDataMangeExcelHeader.setExcelData(sqfwFormSummaryOnlines.get(0).getExcelData());
            updateIgnoreNull(sqfwDataMangeExcelHeader);
        }

        // 清洗param_data数据
        CommunityDataCleanUtils.cleanParamData(sqfwFormSummaryNewList, onlineList, dictFieldAll);

        // 组装excel数据,需要排序，所以插入表操作分开写
        List<Sqfw_datamange_community> sqfwFormSummaryOnlines = Collections.synchronizedList(new ArrayList<Sqfw_datamange_community>());
        buildExcelData(onlineList, sqfwFormSummaryOnlines, type);

        // 插入表
        excelDataBatchInsert(sqfwFormSummaryOnlines, sqfwFormSummaryNewList);
    }

    @Override
    public void cleanDataBySummaryId(String summaryId) {
        // 异步解析入数据管理模块
        try {
            ExecutorService executorService = ThreadPoolExecutorUtil.getInstance();
            CompletableFuture.runAsync(() -> {
                cleanDataBySummaryId(Arrays.asList(summaryId));
            }, executorService);
        } catch (Exception ex) {
            log.error("clean data service error! id : {}", JSONUtil.toJsonStr(summaryId));
        }
    }

    @Aop(TransAop.READ_COMMITTED)
    @Override
    public void cleanDataBySummaryId(List<String> ids) {
        // 参数检查
        if (ObjectUtils.isEmpty(ids)) {
            log.error("====id参数空===={}，{}", ids.toString());
            return;
        }
        List<Sqfw_formSummary_new> sqfwFormSummaryNewList = sqfwFormsummaryNewService.query(Cnd.where("id", "IN", ids));
        if (sqfwFormSummaryNewList.size() <= 0) {
            log.info("未查找到summary数据，{}", ids);
            return;
        }
        String type = sqfwFormSummaryNewList.get(0).getType();
        // 如果是“安全排查整治”则改成“安全排查”
        if (TypeToPKeyEnum.GUANSHANHU_SECURITY_CHK.getType().equals(type)) {
            type = type.substring(0, type.length() - 2);
        }
        // 配置开关检查
        Sqfw_datamange_config sqfwDatamangeConfig = dataMangeConfigService.fetch(
                Cnd.where("type", "LIKE", "%" + type + "%").and("sub_type", "=", "all").and(Sqfw_datamange_config::getDelFlag, "=", false));
        if (null == sqfwDatamangeConfig) {
            log.error("====data_config参数不存在或已失效===={}====", type);
            return;
        }

        // 获取所有相关数据字段
        LinkedHashMap<String, String> dictFieldAll = CommunityDataCleanUtils.queryDictFieldInfoByAll();
        if (null == dictFieldAll) {
            log.info("===============未查询到对应的数据字典field all==============={}====", dictFieldAll.size());
            return;
        }

        // 如果是门前三包， 则需要去低代码config表中取 自定义字段 来和 总表字段汇总
        LinkedHashMap<String, String> fieldMap = new LinkedHashMap<>();
        String configJson = CommunityDataCleanUtils.queryDynamicFieldInfoByType(type);
        CommunityDataCleanUtils.parseDictFeildNew(configJson, fieldMap);
        log.info("==============动态字段config====={}={}={}====", type, configJson, JSONUtil.toJsonStr(fieldMap));

        // 检查数据管理配置表中是否有新字段变化
        CommunityDataCleanUtils.checkDataManageConfigField(type, "all", dictFieldAll, fieldMap);
        log.info("===============数据字段：{}===============", dictFieldAll.size());

        // 拼接全量字段数据
        CommunityDataCleanUtils.checkDataManageConfigPutFieldAll(dictFieldAll);

        List<SqfwFormSummaryOnlineDTO> onlineList = new ArrayList<SqfwFormSummaryOnlineDTO>();

        // 检查是 该类型是否有对应的excel头,没有才插入
        Sqfw_datamange_community sqfwDataMangeExcelHeader =
                this.fetch(Cnd.where(Sqfw_datamange_community::getExcelSeg, "=", "header"));
        // 如果没有头，则插入，如果有则更新
        if (ObjectUtils.isEmpty(sqfwDataMangeExcelHeader) || StringUtils.isEmpty(sqfwDataMangeExcelHeader.getExcelData())) {
            SqfwFormSummaryOnlineDTO sqfwFormSummaryOnlineDTO = new SqfwFormSummaryOnlineDTO();
            sqfwFormSummaryOnlineDTO.setExcelDataMap(dictFieldAll);
            onlineList.add(sqfwFormSummaryOnlineDTO);
        } else {
            LinkedList<SqfwFormSummaryOnlineDTO> onlineExcelHeaders = new LinkedList<SqfwFormSummaryOnlineDTO>();
            SqfwFormSummaryOnlineDTO sqfwFormSummaryOnlineDTO = new SqfwFormSummaryOnlineDTO();
            sqfwFormSummaryOnlineDTO.setExcelDataMap(dictFieldAll);
            sqfwFormSummaryOnlineDTO.setExcelSeg("header");
            onlineExcelHeaders.add(sqfwFormSummaryOnlineDTO);
            List<Sqfw_datamange_community> sqfwFormSummaryOnlines = Collections.synchronizedList(new ArrayList<Sqfw_datamange_community>());
            buildExcelData(onlineExcelHeaders, sqfwFormSummaryOnlines, type);
            sqfwDataMangeExcelHeader.setExcelData(sqfwFormSummaryOnlines.get(0).getExcelData());
            updateIgnoreNull(sqfwDataMangeExcelHeader);
        }

        // 清洗param_data数据
        CommunityDataCleanUtils.cleanParamData(sqfwFormSummaryNewList, onlineList, dictFieldAll);

        // 组装excel数据,需要排序，所以插入表操作分开写
        List<Sqfw_datamange_community> sqfwFormSummaryOnlines = Collections.synchronizedList(new ArrayList<Sqfw_datamange_community>());
        buildExcelData(onlineList, sqfwFormSummaryOnlines, type);

        // 插入表
        excelDataBatchInsert(sqfwFormSummaryOnlines, sqfwFormSummaryNewList);
    }

    /**
     * 组件excel数据,并插入数据库
     */
    private void buildExcelData(List<SqfwFormSummaryOnlineDTO> onlineList, List<Sqfw_datamange_community> sqfwFormSummaryOnlines, String type) {
        String commonId = R.UU32();
        ThreadPoolExecutor threadPoolExecutor = ThreadPoolExecutorUtil.getInstance();
        CompletableFuture[] cf = onlineList.stream().map(item -> CompletableFuture.runAsync(() ->
                        processExcelData(item, sqfwFormSummaryOnlines, commonId, type), threadPoolExecutor)
                .thenApplyAsync(r -> r)).toArray(CompletableFuture[]::new);
        CompletableFuture.allOf(cf).join();
        log.info("===========================组装Excel报文完成：{}条===========================", sqfwFormSummaryOnlines.size());
       /* onlineList.forEach(item->{
            processExcelData(item, rowCount, numCount, commonId, sqfwOnlineService);
        });*/
    }

    /**
     * excel数据处理
     *
     * @param sqfwFormSummaryOnlineDTO
     * @param sqfwDatamangeCommunities
     * @param commonId
     * @param type
     */
    private void processExcelData(SqfwFormSummaryOnlineDTO sqfwFormSummaryOnlineDTO, List<Sqfw_datamange_community> sqfwDatamangeCommunities,
                                  String commonId, String type) {
        //synchronized (LOCK) {
        List<SqfwExcelOnlineAllDTO> sqfwExcelOnlineAllDTOS = Collections.synchronizedList(new ArrayList<SqfwExcelOnlineAllDTO>());
        String updateDate = CommunityDataCleanUtils.formatDate(String.valueOf(sqfwFormSummaryOnlineDTO.getCreateDate()));
        AtomicInteger colCount = new AtomicInteger(0);
        Map<String, String> paramDataMap = new HashMap<String, String>();
        for (ConcurrentSkipListMap.Entry<String, String> entry : sqfwFormSummaryOnlineDTO.getExcelDataMap().entrySet()) {
            // 值为空，不组装json
            if (StringUtils.isEmpty(entry.getValue())) {
                continue;
            }
            // 解析到excelJson
            SqfwExcelOnlineAllDTO sqfwExcelOnlineAllDTO = new SqfwExcelOnlineAllDTO();
            sqfwExcelOnlineAllDTO = CommunityDataCleanUtils.parseSummaryData(entry, updateDate);
            sqfwExcelOnlineAllDTOS.add(sqfwExcelOnlineAllDTO);
            paramDataMap.put(sqfwExcelOnlineAllDTO.getField(), sqfwExcelOnlineAllDTO.getV());
            colCount.getAndIncrement();
        }
        sqfwFormSummaryOnlineDTO.setExcelData(JSONUtil.toJsonStr(sqfwExcelOnlineAllDTOS));
        sqfwFormSummaryOnlineDTO.setParamData(JSONUtil.toJsonStr(paramDataMap));
        sqfwFormSummaryOnlineDTO.setColNum(String.valueOf(colCount.get()));
        log.info("============id:{}, 总列数：{}============", sqfwFormSummaryOnlineDTO.getSqfwSummaryId(), colCount.getAndIncrement());
        sqfwFormSummaryOnlineDTO.setCommonId(commonId);
        sqfwFormSummaryOnlineDTO.setId(R.UU32());
        sqfwFormSummaryOnlineDTO.setDelFlag(false);
        sqfwFormSummaryOnlineDTO.setSqfwSummaryId(sqfwFormSummaryOnlineDTO.getSqfwSummaryId());
        if (StringUtils.isEmpty(sqfwFormSummaryOnlineDTO.getSqfwSummaryId())) {
            sqfwFormSummaryOnlineDTO.setExcelSeg("header");
        } else {
            sqfwFormSummaryOnlineDTO.setExcelSeg("body");
        }
        if (StringUtils.isNotEmpty(type)) {
            sqfwFormSummaryOnlineDTO.setType(type);
        }
        sqfwDatamangeCommunities.add(sqfwFormSummaryOnlineDTO);
        //}
    }

    /**
     * 将清洗好的Excel数据插入数据库
     */
    @Aop(TransAop.READ_COMMITTED)
    //@CacheRemove
    private void excelDataBatchInsert(List<Sqfw_datamange_community> onlineList, List<Sqfw_formSummary_new> sqfwFormSummaryNews) {
        ThreadPoolExecutor threadPoolExecutor = ThreadPoolExecutorUtil.getInstance();
        CompletableFuture[] cf = onlineList.stream().map(item -> CompletableFuture.runAsync(() -> {
                    checkExistUpdateOrInsert(item, sqfwFormSummaryNews);
                }, threadPoolExecutor)
                .thenApplyAsync(r -> r)).toArray(CompletableFuture[]::new);
        CompletableFuture.allOf(cf).join();
    }

    /**
     * 检查是否有历史巡查记录，有则更新，没有则插入
     */
    private void checkExistUpdateOrInsert(Sqfw_datamange_community sqfwDatamangeCommunity, List<Sqfw_formSummary_new> sqfwFormSummaryNews) {
        // 判断记录是否存在，存在则更新
        StringBuffer updateSB = new StringBuffer();
        updateSB.append(" latest_sqfw_summary_id LIKE '%").append(sqfwDatamangeCommunity.getSqfwSummaryId()).append("%'");
        log.info("APP数据更新EXCEL表SQL, SELECT * FROM shequ.sqfw_datamange_community WHERE {}", updateSB.toString());
        List<Sqfw_datamange_community> sqfwDatamangeUpdate = query(Cnd.where(new Static(updateSB.toString())));
        if (null != sqfwDatamangeUpdate && sqfwDatamangeUpdate.size() == 1) {
            Sqfw_datamange_community sqfwDatamangeCommunityUpdate = new Sqfw_datamange_community();
            BeanUtil.copyProperties(sqfwDatamangeUpdate.get(0), sqfwDatamangeCommunityUpdate);
            sqfwDatamangeCommunityUpdate.setUpdateBy(sqfwDatamangeCommunity.getUpdateBy());
            sqfwDatamangeCommunityUpdate.setUpdateDate(sqfwDatamangeCommunity.getUpdateDate());
            sqfwDatamangeCommunityUpdate.setFillDate(sqfwDatamangeCommunity.getFillDate());
            sqfwDatamangeCommunityUpdate.setExcelData(sqfwDatamangeCommunity.getExcelData());
            sqfwDatamangeCommunityUpdate.setParamData(sqfwDatamangeCommunity.getParamData());
            updateIgnoreNull(sqfwDatamangeCommunityUpdate);
        } else {
            // 检查维度数据是否存在, 一个类型只有一个主键
            boolean hasFlag = false;// 确定唯一标识
            StringBuffer sb = new StringBuffer(" 1 = 1 ");
            if (StringUtils.isNotEmpty(sqfwDatamangeCommunity.getProvince()) && !CommonForAdminConstants.ZHISHU.equals(sqfwDatamangeCommunity.getProvince())) {
                sb.append(" AND province = '").append(sqfwDatamangeCommunity.getProvince()).append("'");
            }
            if (StringUtils.isNotEmpty(sqfwDatamangeCommunity.getCity()) && !CommonForAdminConstants.ZHISHU.equals(sqfwDatamangeCommunity.getCity())) {
                sb.append(" AND city = '").append(sqfwDatamangeCommunity.getCity()).append("'");
            }
            if (StringUtils.isNotEmpty(sqfwDatamangeCommunity.getDistrict()) && !CommonForAdminConstants.ZHISHU.equals(sqfwDatamangeCommunity.getDistrict())) {
                sb.append(" AND district = '").append(sqfwDatamangeCommunity.getDistrict()).append("'");
            }
            if (StringUtils.isNotEmpty(sqfwDatamangeCommunity.getCounty()) && !CommonForAdminConstants.ZHISHU.equals(sqfwDatamangeCommunity.getCounty())) {
                sb.append(" AND county = '").append(sqfwDatamangeCommunity.getCounty()).append("'");
            }
            if (StringUtils.isNotEmpty(sqfwDatamangeCommunity.getCommunity()) && !CommonForAdminConstants.ZHISHU.equals(sqfwDatamangeCommunity.getCommunity())) {
                sb.append(" AND community = '").append(sqfwDatamangeCommunity.getCommunity()).append("'");
            }
            sb.append(" AND type LIKE '").append(sqfwDatamangeCommunity.getType()).append("%'");

            if (StringUtils.isNotEmpty(sqfwDatamangeCommunity.getShopsBaseInfoId())) {
                // 有商铺id则用商铺id,没有则用信用代码
                sb.append(" AND shops_base_info_id = '").append(sqfwDatamangeCommunity.getShopsBaseInfoId()).append("'");
                hasFlag = true;
            } else if (StringUtils.isNotEmpty(sqfwDatamangeCommunity.getParamData())) {
                // 社会信用代码
                try {
                    JSONObject jsonObject = JSONObject.parseObject(sqfwDatamangeCommunity.getParamData());
                    String socialCreditCode = jsonObject.getString("socialCreditCode");
                    String placeName = jsonObject.getString("placeName");
                    String name = jsonObject.getString("name");
                    String address = jsonObject.getString("address");
                    String nameOfTheInterviewee = jsonObject.getString("nameOfTheInterviewee");
                    String visitedName = jsonObject.getString("visitedName");
                    if (StringUtils.isNotEmpty(socialCreditCode)) {
                        sb.append(" AND param_data->>'socialCreditCode' = '").append(socialCreditCode).append("'");
                        hasFlag = true;
                    } else if (StringUtils.isNotEmpty(placeName) && StringUtils.isNotEmpty(address)) {
                        sb.append(" AND param_data->>'placeName' = '").append(placeName).append("'");
                        sb.append(" AND param_data->>'address' = '").append(address).append("'");
                        hasFlag = true;
                    } else if (StringUtils.isNotEmpty(visitedName) && StringUtils.isNotEmpty(address)) {
                        sb.append(" AND param_data->>'visitedName' = '").append(visitedName).append("'");
                        sb.append(" AND param_data->>'address' = '").append(address).append("'");
                        hasFlag = true;
                    } else if (StringUtils.isNotEmpty(nameOfTheInterviewee) && StringUtils.isNotEmpty(address)) {
                        sb.append(" AND param_data->>'nameOfTheInterviewee' = '").append(nameOfTheInterviewee).append("'");
                        sb.append(" AND param_data->>'address' = '").append(address).append("'");
                        hasFlag = true;
                    } else if (StringUtils.isNotEmpty(name) && StringUtils.isNotEmpty(address)) {
                        sb.append(" AND param_data->>'name' = '").append(name).append("'");
                        sb.append(" AND param_data->>'address' = '").append(address).append("'");
                        hasFlag = true;
                    }
                } catch (Exception ex) {
                    log.error("解析param_data异常，{}", ex.getMessage());
                }
            }
            log.info("APP数据入EXCEL表SQL, SELECT * FROM shequ.sqfw_datamange_community WHERE {}", sb.toString());
            List<Sqfw_datamange_community> sqfwDatamangeCommunities = query(Cnd.where(new Static(sb.toString())));
            if (null != sqfwDatamangeCommunities && sqfwDatamangeCommunities.size() == 1 && hasFlag) {
                Sqfw_datamange_community sqfwDatamangeCommunityUpdate = new Sqfw_datamange_community();
                BeanUtil.copyProperties(sqfwDatamangeCommunities.get(0), sqfwDatamangeCommunityUpdate);
                String latestSqfwSummaryId = sqfwDatamangeCommunities.get(0).getLatestSqfwSummaryId();
                if (StringUtils.isNotEmpty(latestSqfwSummaryId)) {
                    if (!latestSqfwSummaryId.contains(sqfwDatamangeCommunity.getSqfwSummaryId())) {
                        latestSqfwSummaryId = latestSqfwSummaryId + "," + sqfwDatamangeCommunity.getSqfwSummaryId();
                    }
                } else {
                    latestSqfwSummaryId = sqfwDatamangeCommunity.getSqfwSummaryId();
                }
                sqfwDatamangeCommunityUpdate.setLatestSqfwSummaryId(latestSqfwSummaryId);
                sqfwDatamangeCommunityUpdate.setUpdateBy(sqfwDatamangeCommunity.getUpdateBy());
                sqfwDatamangeCommunityUpdate.setUpdateDate(sqfwDatamangeCommunity.getUpdateDate());
                sqfwDatamangeCommunityUpdate.setFillDate(sqfwDatamangeCommunity.getFillDate());
                sqfwDatamangeCommunityUpdate.setExcelData(sqfwDatamangeCommunity.getExcelData());
                sqfwDatamangeCommunityUpdate.setParamData(sqfwDatamangeCommunity.getParamData());
                updateIgnoreNull(sqfwDatamangeCommunityUpdate);
            } else {
                log.info("APP数据入EXCEL表SQL-insert, {}", JSONUtil.toJsonStr(sqfwDatamangeCommunity));
                sqfwDatamangeCommunity.setLatestSqfwSummaryId(sqfwDatamangeCommunity.getSqfwSummaryId());
                insert(sqfwDatamangeCommunity);
            }
        }
    }

    /**
     * 构造查询条件
     *
     * @param bean
     * @return
     */
    private String buildQueryCond(SqfwDataMangeVO bean, Sqfw_datamange_community header) {
        StringBuilder sql = new StringBuilder();
        sql.append("select * from shequ.sqfw_datamange_community where excel_seg != 'header' and del_flag = false");
        if (null != bean && StringUtils.isNotEmpty(bean.getType())) {
            sql.append(" AND type LIKE '%").append(bean.getType()).append("%')");
        }
        if (null != bean && StringUtils.isNotEmpty(bean.getShopName())) {
            sql.append(" AND EXISTS (SELECT 1 FROM jsonb_array_elements(excel_data::jsonb) AS elem WHERE elem->>'v' like '%").append(bean.getShopName()).append("%')");
        }
        if (null != bean && StringUtils.isNotEmpty(bean.getCreditCode())) {
            sql.append(" AND EXISTS (SELECT 1 FROM jsonb_array_elements(excel_data::jsonb) AS elem WHERE elem->>'v' like '%").append(bean.getCreditCode()).append("%')");
        }
        if (null != bean && StringUtils.isNotEmpty(bean.getIsOwner())) {
            sql.append(" AND EXISTS (SELECT 1 FROM jsonb_array_elements(excel_data::jsonb) AS elem WHERE elem->>'v' like '%").append(bean.getIsOwner()).append("%')");
        }
        if (null != bean && bean.getTime() != null && bean.getTime().size() > 0) {
            String start = bean.getTime().get(0);
            String end = bean.getTime().get(1);
            JSONArray array = JSONUtil.parseArray(header.getExcelData());
            sql.append(" and (");
            for (int i = 0; i < array.size(); i++) {
                sql.append("(");
                sql.append("(excel_data -> ").append(i).append(" ->> 'd') > ").append("'").append(start).append("'");
                sql.append(" and (excel_data -> ").append(i).append(" ->> 'd') < ").append("'").append(end).append("'");
                sql.append(")");
                if (i < array.size() - 1) {
                    sql.append(" or ");
                }
            }
            sql.append(")");
        }
        return sql.toString();
    }

    /**
     * 连表查询报名活动信息
     *
     * @param bean
     * @return
     */
    public int queryViewInfoCount(SqfwDataMangeVO bean, String viewName) {
        String sqlStr = buildConditionForQueryStr(bean, viewName, "true");
        Sql sql = Sqls.create(sqlStr);
        sql.setCallback(Sqls.callback.integer());
        Dao dao = NutzIocUtils.getBean(Dao.class);
        dao.execute(sql);
        return sql.getInt();
    }

    /**
     * 连表查询报名活动信息
     *
     * @param bean
     * @return
     */
    public List<Sqfw_datamange_community> queryViewInfo(SqfwDataMangeVO bean, String viewName, String countFlag) {
        String sqlStr = buildConditionForQueryStr(bean, viewName, countFlag);
        log.info("queryViewInfo-sqlStr--{}", sqlStr);
        Sql sql = Sqls.create(sqlStr);
        sql.setCallback(new SqlCallback() {
            @Override
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                List<Sqfw_datamange_community> sqfwDatamangeCommunities = new ArrayList<Sqfw_datamange_community>();
                while (rs.next()) {
                    Sqfw_datamange_community sqfwDatamangeCommunity = new Sqfw_datamange_community();
                    for (int i = 0; i < columnCount; i++) {
                        sqfwDatamangeCommunity.setId(rs.getString("id"));
                        sqfwDatamangeCommunity.setSqfwSummaryId(rs.getString("sqfw_summary_id"));
                        sqfwDatamangeCommunity.setProvince(rs.getString("province"));
                        sqfwDatamangeCommunity.setCity(rs.getString("city"));
                        sqfwDatamangeCommunity.setDistrict(rs.getString("district"));
                        sqfwDatamangeCommunity.setCounty(rs.getString("county"));
                        sqfwDatamangeCommunity.setCommunity(rs.getString("community"));
                        sqfwDatamangeCommunity.setType(rs.getString("type"));
                        sqfwDatamangeCommunity.setExcelSeg(rs.getString("excel_seg"));
                        sqfwDatamangeCommunity.setExcelData(rs.getString("excel_data"));
                    }
                    sqfwDatamangeCommunities.add(sqfwDatamangeCommunity);
                }
                return sqfwDatamangeCommunities;
            }
        });
        execute(sql);
        return sql.getList(Sqfw_datamange_community.class);
    }

    /**
     * 获取查询条件
     *
     * @param bean
     * @return
     */
    private String buildConditionForQueryStr(SqfwDataMangeVO bean, String viewName, String countFlag) {
        StringBuffer sb = new StringBuffer();
        sb.append(" SELECT id,sqfw_summary_id,province,city,district,county,community,type, excel_seg, excel_data FROM ").append(viewName).append(" WHERE 1 = 1 ");
        if (StringUtils.isNotEmpty(bean.getExcelSeg())) {
            sb.append(" AND excel_seg = '").append(bean.getExcelSeg()).append("'");
        }
        if (!"header".equals(countFlag)) {
            // 登录用户权限
            Sys_user sysUser = sysUserService.fetch(SecurityUtil.getUserId());
            if (null != sysUser && StringUtils.isNotEmpty(sysUser.getProvince()) && !CommonForAdminConstants.ZHISHU.equals(sysUser.getProvince())) {
                sb.append(" AND province = '").append(sysUser.getProvince()).append("'");
            }
            if (null != sysUser && StringUtils.isNotEmpty(sysUser.getCity()) && !CommonForAdminConstants.ZHISHU.equals(sysUser.getCity())) {
                sb.append(" AND city = '").append(sysUser.getCity()).append("'");
            }
            if (null != sysUser && StringUtils.isNotEmpty(sysUser.getDistrict()) && !CommonForAdminConstants.ZHISHU.equals(sysUser.getDistrict())) {
                sb.append(" AND district = '").append(sysUser.getDistrict()).append("'");
            }
            if (null != sysUser && StringUtils.isNotEmpty(sysUser.getCounty()) && !CommonForAdminConstants.ZHISHU.equals(sysUser.getCounty())) {
                sb.append(" AND county = '").append(sysUser.getCounty()).append("'");
            }
            if (null != sysUser && StringUtils.isNotEmpty(sysUser.getCommunity()) && !CommonForAdminConstants.ZHISHU.equals(sysUser.getCommunity())) {
                sb.append(" AND community = '").append(sysUser.getCommunity()).append("'");
            }
            // 入参范围
            // 省
            if (StringUtils.isNotBlank(bean.getProvince()) && !CommonForAdminConstants.ZHISHU.equals(bean.getProvince())) {
                sb.append(" AND province = '").append(bean.getProvince()).append("' ");
            }
            // 市
            if (StringUtils.isNotBlank(bean.getCity()) && !CommonForAdminConstants.ZHISHU.equals(bean.getCity())) {
                sb.append(" AND city = '").append(bean.getCity()).append("' ");
            }
            // 区
            if (StringUtils.isNotBlank(bean.getDistrict()) && !CommonForAdminConstants.ZHISHU.equals(bean.getDistrict())) {
                sb.append(" AND district = '").append(bean.getDistrict()).append("' ");
            }
            // 街道
            if (StringUtils.isNotBlank(bean.getCounty()) && !CommonForAdminConstants.ZHISHU.equals(bean.getCounty())) {
                sb.append(" AND county = '").append(bean.getCounty()).append("' ");
            }
            // 辖区
            if (StringUtils.isNotBlank(bean.getCommunity()) && !CommonForAdminConstants.ZHISHU.equals(bean.getCommunity())) {
                sb.append(" AND community = '").append(bean.getCommunity()).append("' ");
            }
        }
        if (!"header".equals(bean.getExcelSeg())) {
            if (StringUtils.isNotEmpty(bean.getType())) {
                sb.append(" AND type LIKE '").append(bean.getType()).append("'");
            }
            if (ObjectUtils.isNotEmpty(bean.getIds())) {
                StringBuffer sbIds = new StringBuffer("(");
                bean.getIds().forEach(id -> {
                    sb.append("'").append(id).append("',");
                });
                String ids = sbIds.substring(0, sb.length() - 1) + ")";
                sb.append(" AND id IN ").append(ids);
            }
            if (Strings.isNotBlank(bean.getQueryAll())) {
                sb.append(" AND ( province LIKE '%").append(bean.getQueryAll()).append("%'")
                        .append(" OR city LIKE '%").append(bean.getQueryAll()).append("%'")
                        .append(" OR district LIKE '%").append(bean.getQueryAll()).append("%'")
                        .append(" OR county LIKE '%").append(bean.getQueryAll()).append("%'")
                        .append(" OR community LIKE '%").append(bean.getQueryAll()).append("%')");
            }
            if (StringUtils.isNotEmpty(bean.getShopName())) {
                sb.append(" AND param_data->>'placeName' = '").append(bean.getShopName()).append("' ");
            }
            if (StringUtils.isNotEmpty(bean.getCreditCode())) {
                sb.append(" AND param_data->>'socialCreditCode' = '").append(bean.getCreditCode()).append("' ");
            }
            if (StringUtils.isNotEmpty(bean.getIsOwner())) {
                String isOwner = "";
                if ("0".equals(bean.getIsOwner())) {
                    isOwner = "否";
                } else if ("1".equals(bean.getIsOwner())) {
                    isOwner = "是";
                }
                sb.append(" AND param_data->>'isKitchenMerchant' = '").append(isOwner).append("' ");
            }
            if (StringUtils.isNotEmpty(bean.getPlaceName())) {
                sb.append(" AND param_data->>'placeName' LIKE '%").append(bean.getPlaceName()).append("%' ");
            }
            if (StringUtils.isNotEmpty(bean.getPlaceClassify())) {
                sb.append(" AND param_data->>'type' LIKE '%").append(bean.getPlaceClassify()).append("%' ");
            }
            if (StringUtils.isNotEmpty(bean.getPlaceType())) {
                sb.append(" AND param_data->>'placeType' LIKE '%").append(bean.getPlaceType()).append("%' ");
            }
            if (bean.getTime() != null && bean.getTime().size() > 0) {
                String start = bean.getTime().get(0);
                String end = bean.getTime().get(1);
                sb.append(" AND substring(create_date::text,0,11) >= '").append(start).append("' AND substring(create_date::text,0,11) <= '").append(end).append("' ");
            }
            // 根据类型划分出对应数据
            if (!"all".equals(bean.getSubType()) && StringUtils.isNotEmpty(bean.getType()) && StringUtils.isNotEmpty(bean.getSubType())) {
                String key = TypeToPKeyEnum.getByType(bean.getType(), bean.getSubType()).getKey();
                if (StringUtils.isNotEmpty(key)) {
                    StringBuffer subSb = new StringBuffer();
                    for (String subKey : key.split(",")) {
                        subSb.append(" (char_length(param_data->>'").append(subKey).append("') > 0) OR");
                    }
                    sb.append(" AND (").append(subSb.substring(0, subSb.length() - 2)).append(") ");
                }
            }
            // 是否有隐患
            if (StringUtils.isNotEmpty(bean.getType()) && StringUtils.isNotEmpty(bean.getSubType()) && StringUtils.isNotEmpty(bean.getIsHiddenDanger())) {
                String hiddenDangerSql = DataManageHiddenDangersEnum.getSqlCondByType(bean.getType(), bean.getSubType(), bean.getIsHiddenDanger());
                if (StringUtils.isNotEmpty(hiddenDangerSql)) {
                    sb.append(hiddenDangerSql);
                }
            }
            // 姓名
            if (StringUtils.isNotEmpty(bean.getName())) {
                sb.append(" AND param_data->>'name' LIKE '%").append(bean.getName()).append("%' ");
            }
            // 关爱类别
            if (StringUtils.isNotEmpty(bean.getCareType())) {
                sb.append(" AND param_data->>'careCategory' LIKE '%").append(bean.getCareType()).append("%' ");
            }
            // 家庭是否困难
            if (StringUtils.isNotEmpty(bean.getDifficult())) {
                sb.append(" AND param_data->>'familyDifficulties' = '").append(bean.getDifficult()).append("' ");
            }
            // 地址
            if (StringUtils.isNotEmpty(bean.getAddress())) {
                sb.append(" AND param_data->>'address' LIKE '%").append(bean.getAddress()).append("%' ");
            }
            // 走访时间
            if (ObjectUtil.isNotEmpty(bean.getQueryStartTime()) && ObjectUtil.isNotEmpty(bean.getQueryEndTime())) {
                String startTime = LocalDateTimeUtil.format(bean.getQueryStartTime(), DatePattern.NORM_DATE_PATTERN) + " 00:00:00";
                String endTime = LocalDateTimeUtil.format(bean.getQueryEndTime(), DatePattern.NORM_DATE_PATTERN) + " 59:59:59";
                if (DataManageConstants.TYPE_SOCIETY_SECURITY.equals(bean.getType())) {
                    sb.append(" AND param_data->>'inspectionDate' >= '").append(startTime).append("' AND param_data->>'inspectionDate' <= '").append(endTime).append("' ");
                } else {
                    sb.append(" AND param_data->>'fillDate' >= '").append(startTime).append("' AND param_data->>'fillDate' <= '").append(endTime).append("' ");
                }
            }
            // 案件类型
            if (StringUtils.isNotEmpty(bean.getCaseType())) {
                sb.append(" AND param_data->>'caseType' LIKE '%").append(bean.getCaseType()).append("%' ");
            }
            // 巡查类型
            if (StringUtils.isNotEmpty(bean.getPatrolType())) {
                sb.append(" AND param_data->>'patrolType' LIKE '%").append(bean.getPatrolType()).append("%' ");
            }
            // 记录类型
            if (StringUtils.isNotEmpty(bean.getRecordType())) {
                sb.append(" AND param_data->>'type' LIKE '%").append(bean.getRecordType()).append("%' ");
            }
            // 记录类型
            if (StringUtils.isNotEmpty(bean.getTheme())) {
                sb.append(" AND param_data->>'theme' LIKE '%").append(bean.getTheme()).append("%' ");
            }
            // 拜访人员姓名
            if (StringUtils.isNotEmpty(bean.getVisitedName())) {
                sb.append(" AND param_data->>'visitedName' LIKE '%").append(bean.getVisitedName()).append("%' ");
            }
            // 儿童类别
            if (StringUtils.isNotEmpty(bean.getChildType())) {
                sb.append(" AND param_data->>'childrenType' LIKE '%").append(bean.getChildType()).append("%' ");
            }
            // 老年探访，被探访人姓名
            if (StringUtils.isNotEmpty(bean.getNameOfTheInterviewee())) {
                sb.append(" AND param_data->>'nameOfTheInterviewee' LIKE '%").append(bean.getNameOfTheInterviewee()).append("%' ");
            }
            // 老年探访，是否空巢老人
            if (StringUtils.isNotEmpty(bean.getLiveAloneOrNot())) {
                sb.append(" AND param_data->>'liveAloneOrNot' LIKE '%").append(bean.getLiveAloneOrNot()).append("%' ");
            }
            // 养老服务站巡查，营业状态
            if (StringUtils.isNotEmpty(bean.getBusinessStatus())) {
                sb.append(" AND param_data->>'businessStatus' LIKE '%").append(bean.getBusinessStatus()).append("%' ");
            }
            // 养老服务站巡查，餐饮店面销售类型
            if (StringUtils.isNotEmpty(bean.getFileOutputType())) {
                sb.append(" AND param_data->>'fileOutputType' LIKE '%").append(bean.getFileOutputType()).append("%' ");
            }
        }
        if ("true".equals(countFlag)) {
            String resultStr = "";
            // 查询总数
            resultStr = "SELECT COUNT(t1.*) FROM (" + sb.toString() + ") t1";
            return resultStr;
        } else if ("false".equals(countFlag)) {
            sb.append(" ORDER BY create_date DESC ");
            Integer pageNo = bean.getPageNo();
            Integer pageSize = bean.getPageSize();
            if (1 == pageNo) {
                pageNo = 0;
            }
            if (pageNo > 1) {
                pageNo = (pageNo - 1) * pageSize;
            }
            if (0 != pageSize) {
                sb.append(" LIMIT ").append(pageSize).append(" OFFSET ").append(pageNo);
            }
        } else if ("all".equals(countFlag)) {
            sb.append(" ORDER BY create_date DESC ");
            sb.append(" AND excel_seg != 'header'");
            sb.append(" LIMIT ").append(0).append(" OFFSET ").append(500);
        } else if ("header".equals(countFlag)) {
            sb.append(" AND excel_seg = 'header'");
        }
        return sb.toString();
    }

    /**
     * 查表头
     *
     * @param bean
     * @return
     */
    public Sqfw_datamange_community queryHeaderInfo(SqfwDataMangeVO bean) {
        String sqlStr = "WITH config_fields AS (SELECT view_fields FROM shequ.Sqfw_datamange_config WHERE type LIKE @type AND sub_type = @subType AND del_flag = @delFlag) SELECT id,sqfw_summary_id,province,city,district,county,community,type,excel_seg,create_date, '[' || string_agg(sub_json::text, ', ') || ']' as excel_data FROM (SELECT id,sqfw_summary_id,province,city,district,county,community,type,excel_seg,create_date,jsonb_array_elements(sdc.excel_data::jsonb) AS sub_json FROM shequ.sqfw_datamange_community sdc WHERE EXISTS ( SELECT 1 FROM jsonb_array_elements(sdc.excel_data::jsonb) AS elem WHERE elem->>'field' IN (SELECT unnest(string_to_array(view_fields, ',')) AS field FROM config_fields)) AND excel_seg = 'header' ) subquery WHERE subquery.sub_json->>'field' IN (SELECT unnest(string_to_array(view_fields, ',')) AS field FROM config_fields) GROUP BY id,sqfw_summary_id,province,city,district,county,community,type,excel_seg,create_date";
        Sql sql = Sqls.create(sqlStr);
        sql.setParam("type", "%" + bean.getType() + "%");
        sql.setParam("subType", bean.getSubType());
        sql.setParam("delFlag", false);
        log.info("queryViewInfo-sqlStr--{}", sql.toString());
        sql.setCallback(new SqlCallback() {
            @Override
            public Object invoke(Connection conn, ResultSet rs, Sql sql) throws SQLException {
                ResultSetMetaData metaData = rs.getMetaData();
                int columnCount = metaData.getColumnCount();
                List<Sqfw_datamange_community> sqfwDatamangeCommunities = new ArrayList<Sqfw_datamange_community>();
                while (rs.next()) {
                    Sqfw_datamange_community sqfwDatamangeCommunity = new Sqfw_datamange_community();
                    for (int i = 0; i < columnCount; i++) {
                        sqfwDatamangeCommunity.setId(rs.getString("id"));
                        sqfwDatamangeCommunity.setSqfwSummaryId(rs.getString("sqfw_summary_id"));
                        sqfwDatamangeCommunity.setProvince(rs.getString("province"));
                        sqfwDatamangeCommunity.setCity(rs.getString("city"));
                        sqfwDatamangeCommunity.setDistrict(rs.getString("district"));
                        sqfwDatamangeCommunity.setCounty(rs.getString("county"));
                        sqfwDatamangeCommunity.setCommunity(rs.getString("community"));
                        sqfwDatamangeCommunity.setType(rs.getString("type"));
                        sqfwDatamangeCommunity.setExcelSeg(rs.getString("excel_seg"));
                        sqfwDatamangeCommunity.setExcelData(rs.getString("excel_data"));
                    }
                    sqfwDatamangeCommunities.add(sqfwDatamangeCommunity);
                }
                return sqfwDatamangeCommunities;
            }
        });
        execute(sql);
        return sql.getObject(Sqfw_datamange_community.class);
    }

    @Override
    public Map<String, String> getUserTopAreaLevelId() {
        Map<String, String> map = new HashMap<String, String>();
        String areaId = "";
        Sys_user sysUser = sysUserService.fetch(SecurityUtil.getUserId());
        if (null == sysUser || CommonForAdminConstants.ZHISHU.equals(sysUser.getProvince()) || "".equals(sysUser.getProvince())) {
            return map;
        }
        // 获取省级id
        Sys_area sysArea = sysAreaService.fetch(Cnd.where(Sys_area::getName, "=", sysUser.getProvince()));
        if (null != sysArea) {
            map.put("id", sysArea.getId());
            map.put("name", sysArea.getName());
            map.put("type", TreeDTO.P);
        } else {
            return map;
        }
        if (!CommonForAdminConstants.ZHISHU.equals(sysUser.getCity())) {
            sysArea = sysAreaService.fetch(Cnd.where(Sys_area::getName, "=", sysUser.getCity()).and(Sys_area::getParentId, "=", sysArea.getId()));
            if (null != sysArea) {
                map.put("id", sysArea.getId());
                map.put("name", sysArea.getName());
                map.put("type", TreeDTO.C);
            } else {
                return map;
            }
        }
        if (!CommonForAdminConstants.ZHISHU.equals(sysUser.getDistrict())) {
            sysArea = sysAreaService.fetch(Cnd.where(Sys_area::getName, "=", sysUser.getDistrict()).and(Sys_area::getParentId, "=", sysArea.getId()));
            if (null != sysArea) {
                map.put("id", sysArea.getId());
                map.put("name", sysArea.getName());
                map.put("type", TreeDTO.D);
            } else {
                return map;
            }
        }
        if (!CommonForAdminConstants.ZHISHU.equals(sysUser.getCounty())) {
            sysArea = sysAreaService.fetch(Cnd.where(Sys_area::getName, "=", sysUser.getCounty()).and(Sys_area::getParentId, "=", sysArea.getId()));
            if (null != sysArea) {
                map.put("id", sysArea.getId());
                map.put("name", sysArea.getName());
                map.put("type", TreeDTO.J);
            } else {
                return map;
            }
        }
        if (!CommonForAdminConstants.ZHISHU.equals(sysUser.getCommunity())) {
            sysArea = sysAreaService.fetch(Cnd.where(Sys_area::getName, "=", sysUser.getCommunity()).and(Sys_area::getParentId, "=", sysArea.getId()));
            if (null != sysArea) {
                map.put("id", sysArea.getId());
                map.put("name", sysArea.getName());
                map.put("type", TreeDTO.S);
            } else {
                return map;
            }
        }
        return map;
    }
}


